-- Tinh
CREATE PROCEDURE Tinh_load
AS
BEGIN
	SELECT * FROM Tinh
END
go
CREATE PROC Tinh_them(

@maTinh varchar(10),
@tenTinh ntext
)
AS
BEGIN
	INSERT INTO Tinh VALUES (@maTinh, @tenTinh) 
END
go


--Hop Dong
CREATE PROCEDURE HopDong_load
AS
BEGIN
	SELECT * FROM HopDong
END
go
CREATE PROCEDURE KhachHang_load
AS
BEGIN
	SELECT * FROM KhachHang
END
go
CREATE PROCEDURE KhachSan_load
AS
BEGIN
	SELECT * FROM KhachSan
END
go
CREATE PROCEDURE NhaHang_load
AS
BEGIN
	SELECT * FROM NhaHang
END
go
CREATE PROCEDURE NhanVien_load
AS
BEGIN
	SELECT * FROM NhanVien
END
-- Tour
go
CREATE PROCEDURE Tour_load
AS
BEGIN
		SELECT Tour.maTour, Tour.tenTour, Tour.noiKhoiHanh, Tour.moTa, Tour.loaiHinh, Tour.maAnh, Tour.giaCa, DiaDanh.tenDiaDanh, NhaHang.tenNhaHang, KhachSan.tenKhachSan, VanTai.tenVanTai
FROM Tour, DiaDanh, ChiTiet_Tour_DiaDanh, NhaHang, ChiTiet_Tour_NhaHang, KhachSan, ChiTiet_Tour_KhachSan, VanTai, ChiTiet_Tour_VanTai
WHERE Tour.maTour = ChiTiet_Tour_DiaDanh.maTour and ChiTiet_Tour_DiaDanh.maDiaDanh = DiaDanh.maDiaDanh
	and Tour.maTour = ChiTiet_Tour_NhaHang.maTour and ChiTiet_Tour_NhaHang.maNhaHang = NhaHang.maNhaHang
	and Tour.maTour = ChiTiet_Tour_KhachSan.maTour and ChiTiet_Tour_KhachSan.maKhachSan = KhachSan.maKhachSan
	and Tour.maTour = ChiTiet_Tour_VanTai.maTour and ChiTiet_Tour_VanTai.maVanTai = VanTai.maVanTai
END
go
--CREATE PROCEDURE Tour_load_1
--AS
--BEGIN
--	SELECT Tour.
--END
go
CREATE PROC Tour_timKiem
@maTour int
AS
BEGIN
	SELECT * FROM Tour WHERE maTour = @maTour
END

go
CREATE PROC Tour_them(

@maDiaDanh int,
@maNhaHang int,
@maVanTai int,
@maKhachSan int,
@tenTour ntext,
@noiKhoiHanh ntext,
@giaCa money,
@moTa ntext,
@loaiHinh ntext,
@maAnh int
)
AS
BEGIN
	INSERT INTO Tour(maDiaDanh, maNhaHang, maVanTai, maKhachSan, tenTour, noiKhoiHanh, giaCa, moTa, loaiHinh, maAnh) VALUES (@maDiaDanh, @maNhaHang, @maVanTai, @maKhachSan, @tenTour, @noiKhoiHanh, @giaCa, @moTa, @loaiHinh, @maAnh ) 
END
go

CREATE PROC Tour_sua(

@maDiaDanh int,
@maNhaHang int,
@maVanTai int,
@maKhachSan int,
@tenTour ntext,
@noiKhoiHanh ntext,
@giaCa money,
@moTa ntext,
@loaiHinh ntext,
@maAnh int
)
AS
BEGIN
	UPDATE Tour SET  maNhaHang = @maNhaHang , maVanTai = @maVanTai, maKhachSan = @maKhachSan, tenTour = @tenTour, noiKhoiHanh = @noiKhoiHanh, giaCa = @giaCa, moTa = @moTa, loaiHinh = @loaiHinh, maAnh = @maAnh
	WHERE	maDiaDanh = @maDiaDanh
END
go
CREATE PROCEDURE Tour_xoa
@maTour int
AS
BEGIN
	DELETE FROM Tour WHERE maTour = @maTour;
END

go
CREATE PROCEDURE VanTai_load
AS
BEGIN
	SELECT * FROM VanTai
END
go
CREATE PROCEDURE Ve_load
AS
BEGIN
	SELECT * FROM Ve
END
go
CREATE PROCEDURE DiaDanh_load
AS
BEGIN
	SELECT * FROM DiaDanh
END
-- anh
CREATE PROCEDURE Anh_load
AS
BEGIN
	SELECT * FROM HinhAnh
END 

GO
SELECT Tour.maTour, Tour.tenTour, Tour.noiKhoiHanh, Tour.moTa, Tour.loaiHinh, Tour.maAnh, Tour.giaCa, DiaDanh.tenDiaDanh, NhaHang.tenNhaHang, KhachSan.tenKhachSan, VanTai.tenVanTai
FROM Tour, DiaDanh, ChiTiet_Tour_DiaDanh, NhaHang, ChiTiet_Tour_NhaHang, KhachSan, ChiTiet_Tour_KhachSan, VanTai, ChiTiet_Tour_VanTai
WHERE Tour.maTour = ChiTiet_Tour_DiaDanh.maTour and ChiTiet_Tour_DiaDanh.maDiaDanh = DiaDanh.maDiaDanh
	and Tour.maTour = ChiTiet_Tour_NhaHang.maTour and ChiTiet_Tour_NhaHang.maNhaHang = NhaHang.maNhaHang
	and Tour.maTour = ChiTiet_Tour_KhachSan.maTour and ChiTiet_Tour_KhachSan.maKhachSan = KhachSan.maKhachSan
	and Tour.maTour = ChiTiet_Tour_VanTai.maTour and ChiTiet_Tour_VanTai.maVanTai = VanTai.maVanTai
